package DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import DataObjects.RecipeLeftList;
import DataObjects.RecipeRightList;

public class GetAllItemsOfThisRecipe  {
	
	
	private  String noRecipe;
	
	private RecipeRightList  row;
	private Vector<String>  row1;
	private Vector<RecipeLeftList>  resTableLeftList;
	private Vector<RecipeRightList>  resTableRightList;
	
	public GetAllItemsOfThisRecipe(String NumberRecipe)
	{
		this.noRecipe=NumberRecipe;
		
	}

	public Vector<RecipeRightList> getItemsOfRecipe()
	{
		SQLConnection sc=new SQLConnection();
			
		try {
			
			
			
			
			PreparedStatement ps1 = sc.getConnection().prepareStatement("Select iname,it.icost,TRUNCATE(it.iCost*ir.iquantity,1) as price, IUnit_of_measure,IQuantity,it.Iid  From item_in_recipe ir,item it   Where ir.rid=? AND ir.iid=it.iid");	
			
			ps1.setString(1,noRecipe);
			
			ResultSet rs=ps1.executeQuery();
			
			
			
			resTableRightList = new Vector<RecipeRightList>();
			
			 while(rs.next() )
		        {   
				 row = new RecipeRightList();
				 
				                  	                
		               row.setItemName( rs.getString(1));
		               row.setiCost(rs.getFloat(2));
		               row.setiReallyCost(rs.getFloat(3));
		               row.setiUnit_of_measure(rs.getString(4));
		               row.setiQuantity(rs.getFloat(5));
		               row.setItemID(rs.getInt(5));
                                        
		         	
		               resTableRightList.add(row);
				 
				
		        }
			 
			 rs.close();
			 
			ps1.close();
			 sc.getConnection().close();
			
				}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		
		
	
		
		
		return 	resTableRightList;
		
	}
	
	public Vector<RecipeLeftList> getAllItems()
	{
		
		
      SQLConnection sc=new SQLConnection();
      
		try {
			
		
	//	PreparedStatement ps = sc.getConnection().prepareStatement("SELECT Iid,Iname , IUnit_of_measure ,IStoreQuantity,Iid  FROM item it WHERE it.Iid NOT in (SELECT i.Iid FROM  item_in_recipe i  WHERE i.Rid=?);");
		PreparedStatement ps = sc.getConnection().prepareStatement("SELECT it.Iid ,it.Iname,ICategory,IExpiration,IMin_quantity,IMax_quantity,IUnit_of_measure,Icost FROM item it WHERE it.Iid NOT in (SELECT i.Iid FROM  item_in_recipe i  WHERE i.Rid=?);");	
        ps.setString(1,noRecipe);
		ResultSet rs=ps.executeQuery();	
		 
		
		resTableLeftList = new Vector<RecipeLeftList>();
		
		 while(rs.next())
	        {   

			 RecipeLeftList  row = new RecipeLeftList();
	                            	                	               
			 row.setItemID(rs.getInt(1));
             row.setItemName(rs.getString(2));
             row.setiCategory(rs.getString(3));
             row.setiExpiration(rs.getString(4));
             row.setiMin(rs.getFloat(5));
             row.setiMax(rs.getFloat(6));
             row.setIUnit_of_measure(rs.getString(7));
             row.setiCost(rs.getFloat(8));	               
	         	
             resTableLeftList.add(row);
	        
	        }//while
		 ps.close();
		 rs.close();
		 sc.getConnection().close();
		
		} catch (SQLException e) {
				
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			
		return resTableLeftList;
		
	}
		
	}
